﻿using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class CommonResource : Basic, ICommonResource
    {
        private const string sqlGetComAll = "select * from CommonResource where type!=1";

        private const string sqlGetOpsAll = "select * from CommonResource Where type=1 and siteid like '%{0}%'";

        private const string sqlDeleteFile = "Delete  FROM  CommonResource WHERE ID=:id";

        private const string sqlGetbyKeywords = "select * from (select t.*,rownum rn from (SELECT {0} FROM  CommonResource where (keywords like '%{1}%' or dname like '%{1}%' or filename like '%{1}%') and type !='1' {2}) t ) {3}";

        private const string sqlGetOpsbyKeywords = "select * from (select t.*,rownum rn from (SELECT {0} FROM  CommonResource where (keywords like '%{1}%' or dname like '%{1}%' or filename like '%{1}%') and type ='1' {2}) t ) {3}";

        public List<CommonResourceModel> GetPlanByKeywords(out decimal totalCount, string keywords, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
            {
                var selectColumn = string.Empty;
                var props = typeof(CommonResourceModel).GetProperties();
                foreach (var p in props)
                {
                    selectColumn += p.Name + ',';
                }

                var orderName = " asc ";
                if (!string.IsNullOrWhiteSpace(order))
                {
                    if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                        order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                    {
                        orderName = " desc ";
                    }
                }

                var condition = " order by " + sort + " " + orderName;

                int beginIndex = (pageIndex.Value - 1) * pageSize.Value + 1;
                int endIndex = beginIndex + pageSize.Value - 1;

                var countSql = string.Format(sqlGetbyKeywords, "count(*)", keywords, condition, "");

                var sql = string.Format(sqlGetbyKeywords, selectColumn.TrimEnd(','), keywords, condition, " where rn between " + beginIndex + " and " + endIndex + "");

                var list = ModelHelper<CommonResourceModel>.GetModel_By_DirectSql(sql, null, conitsm);

                totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_DirectSql(countSql, null, conitsm);

                return list;

            }
        }

        public List<CommonResourceModel> GetAllCommonResources(out decimal totalCount, int? pageIndex, int? pageSize, string sort = "CREATETIME", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "TYPE",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = "1",
                QueryOperator = QueryOperatorType.notEqual,
                PredicateType = PredicateType.where
            });
            totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        public List<CommonResourceModel> GetOpsByKeywords(out decimal totalCount, string keywords, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
            {
                var selectColumn = string.Empty;
                var props = typeof(CommonResourceModel).GetProperties();
                foreach (var p in props)
                {
                    selectColumn += p.Name + ',';
                }

                var orderName = " asc ";
                if (!string.IsNullOrWhiteSpace(order))
                {
                    if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                        order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                    {
                        orderName = " desc ";
                    }
                }

                var condition = " order by " + sort + " " + orderName;

                int beginIndex = (pageIndex.Value - 1) * pageSize.Value + 1;
                int endIndex = beginIndex + pageSize.Value - 1;

                var countSql = string.Format(sqlGetOpsbyKeywords, "count(*)", keywords, condition, "");

                var sql = string.Format(sqlGetOpsbyKeywords, selectColumn.TrimEnd(','), keywords, condition, " where rn between " + beginIndex + " and " + endIndex + "");

                var list = ModelHelper<CommonResourceModel>.GetModel_By_DirectSql(sql, null, conitsm);

                totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_DirectSql(countSql, null, conitsm);

                return list;

            }
        }

        public List<CommonResourceModel> GetAllOpsDocuments(string siteid,out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Type",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = "1",
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "SiteID",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = siteid,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });
            totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        //public List<CommonResourceModel> GetPlanByKeywords(string keywords, int? pageIndex, int? pageSize, out decimal totalCount, string sort = "ID", string order = "Descending")
        //{
        //    OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
        //    List<QueryParameter> attParam = new List<QueryParameter>() { 
        //        new QueryParameter(){OracleDbColumnName="JobOwnerID", OracleDbColumnType=OracleDbType.Int64,
        //                OracleDbColumnValue=keywords,QueryOperator=QueryOperatorType.like,PredicateType=PredicateType.where},
        //              };
        //    totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_QueryParams(attParam, oda);
        //    return ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        //}

        public List<CommonResourceModel> SearchCom(string keywords, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            var ret = new List<CommonResourceModel>();
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();
            JoinCauses joins = new JoinCauses();

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "DName",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Keywords",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "FileName",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Type",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = "1",
                QueryOperator = QueryOperatorType.notEqual,
                PredicateType = PredicateType.where
            });

            totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_QueryParams(attParam, oda);
            ret = ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
            return ret;
        }

        public List<CommonResourceModel> SearchOps(string keywords, out decimal totalCount, int? pageIndex, int? pageSize, string sort = "ID", string order = "Descending")
        {
            var ret = new List<CommonResourceModel>();
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            List<QueryParameter> attParam = new List<QueryParameter>();
            JoinCauses joins = new JoinCauses();

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "DName",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Keywords",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "FileName",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = keywords,
                QueryOperator = QueryOperatorType.like,
                PredicateType = PredicateType.where
            });
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Type",
                OracleDbColumnType = OracleDbType.Varchar2,
                OracleDbColumnValue = "1",
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });

            totalCount = ModelHelper<CommonResourceModel>.GetModelCount_By_QueryParams(attParam, oda);
            ret = ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
            return ret;
        }


        public CommonResourceModel GetByID(int id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="id", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=id,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<CommonResourceModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public List<CommonResourceModel> GetAllComInfo()
        {
            return ModelHelper<CommonResourceModel>.GetModel_By_DirectSql(sqlGetComAll, null, oda);
        }

        public List<CommonResourceModel> GetAllOpsInfo(string siteid)
        {
            return ModelHelper<CommonResourceModel>.GetModel_By_DirectSql(sqlGetOpsAll, null, oda);
        }

        public int Add(CommonResourceModel model)
        {
            return ModelHelper<CommonResourceModel>.AddModel_By_QueryParams(model, new List<string>() { "ID" }, oda);
        }

        public bool Delete(int ID)
        {
            int result = oda.ExecuteCommand(sqlDeleteFile, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="id",
                        OracleDbType = OracleDbType.Decimal,
                        Value = (decimal)ID
                    }
                });
            return result == 1;
        }

        public int Update(CommonResourceModel model)
        {
            return ModelHelper<CommonResourceModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=model.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
            }, model, new List<string>() { "ID" }, oda);
        }

        public int AddList(IList<CommonResourceModel> models)
        {
            return ModelHelper<CommonResourceModel>.AddListModel_By_QueryParams(models, new List<string>() { "ID" }, oda);
        }
    }
}
